﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class TeachersDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoTeachers model)
        {
            model.TEA_ID = System.Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Teachers]");
            sql.Append("([TEA_ID],[TEA_CODE],[TEA_NAME],[TEA_USERNAME],[TEA_PASSWORD],[TEA_GENDER],[TEA_ACADEMY],[TEA_MANAGER],[TEA_PHONE],[TEA_EMAIL])");
            sql.Append("VALUES");
            sql.Append("(@TEA_ID,@TEA_CODE,@TEA_NAME,@TEA_USERNAME,@TEA_PASSWORD,@TEA_GENDER,@TEA_ACADEMY,@TEA_MANAGER,@TEA_PHONE,@TEA_EMAIL)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoTeachers model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Teachers] SET ");
            sql.Append("TEA_CODE=@TEA_CODE");
            sql.Append(",TEA_NAME=@TEA_NAME");
            sql.Append(",TEA_USERNAME=@TEA_USERNAME");
            sql.Append(",TEA_PASSWORD=@TEA_PASSWORD");
            sql.Append(",TEA_GENDER=@TEA_GENDER");
            sql.Append(",TEA_ACADEMY=@TEA_ACADEMY");
            sql.Append(",TEA_MANAGER=@TEA_MANAGER");
            sql.Append(",TEA_PHONE=@TEA_PHONE");
            sql.Append(",TEA_EMAIL=@TEA_EMAIL");
            sql.Append(" WHERE ");
            sql.Append("TEA_ID=@TEA_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string tEA_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("update [Teachers] SET");
            sql.Append(" TEA_IsShow=0");
            sql.Append(" WHERE ");
            sql.Append("TEA_ID=@TEA_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@TEA_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = tEA_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public BoTeachers GetModel(string tEA_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT TEA_ID,TEA_CODE,TEA_NAME,TEA_USERNAME,TEA_PASSWORD,TEA_GENDER,TEA_ACADEMY,TEA_MANAGER,A.ACA_NAME,TEA_PHONE,TEA_EMAIL ");
            sql.Append(" FROM Teachers AS T LEFT JOIN Academy AS A ON T.TEA_ACADEMY=A.ACA_ID ");
            sql.Append(" WHERE ");
            sql.Append("TEA_ID=@TEA_ID AND TEA_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@TEA_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = tEA_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoTeachers> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }
        /// <summary>
        /// 通过学院id来删除教师
        /// </summary>
        /// <param name="localId"></param>
        /// <returns>返回受影响的行数</returns>
        public int DeleteTeacherByAcademyId(string academyId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Teachers] SET ");
            sql.Append(" TEA_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append("TEA_ACADEMY=@TEA_ACADEMY");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@TEA_ACADEMY", SqlDbType.NVarChar, 50);
            parameter[0].Value = academyId;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }
        public BoTeachers GetTeacherByUserName(string userName)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT TEA_ID,TEA_CODE,TEA_NAME,TEA_USERNAME,TEA_PASSWORD,TEA_GENDER,TEA_ACADEMY,TEA_MANAGER,A.ACA_NAME,TEA_PHONE,TEA_EMAIL ");
            sql.Append(" FROM Teachers AS T LEFT JOIN Academy AS A ON T.TEA_ACADEMY=A.ACA_ID ");
            sql.Append(" WHERE TEA_USERNAME=@TEA_USERNAME AND TEA_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@TEA_USERNAME", SqlDbType.NVarChar, 50);
            parameter[0].Value = userName;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoTeachers> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoTeachers> GetAllTeachers()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT TEA_ID,TEA_CODE,TEA_NAME,TEA_USERNAME,TEA_PASSWORD,TEA_GENDER,TEA_ACADEMY,TEA_MANAGER,A.ACA_NAME,TEA_PHONE,TEA_EMAIL ");
            sql.Append("FROM Teachers AS T LEFT JOIN Academy AS A ON T.TEA_ACADEMY=A.ACA_ID ");
            sql.Append("where TEA_IsShow='true'");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoTeachers> list = GetList(table);
            return list;
        }

         /// <summary>
        /// 得到分页的数据列表   
        /// </summary>
        /// <param name="orderCon">排序条件</param>
        /// <param name="sqlCon">查询条件</param>
        /// <param name="pagesize">每页显示大小</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="records">返回查询到的记录</param>
        /// <returns>返回对象列表</returns>
        public IList<BoTeachers> getPageCompetitions(string sqlCon, string orderCon, int pagesize, int pageIndex, out int records)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT TEA_ID,TEA_CODE,TEA_NAME,TEA_USERNAME,TEA_PASSWORD,TEA_GENDER,TEA_ACADEMY,TEA_MANAGER,A.ACA_NAME,TEA_PHONE,TEA_EMAIL ");
            sql.Append("FROM Teachers AS T LEFT JOIN Academy AS A ON T.TEA_ACADEMY=A.ACA_ID ");
            sql.Append("where TEA_IsShow='true' ");
            if (!string.IsNullOrEmpty(sqlCon))
            {
                sql.Append("and ");
                sql.Append(sqlCon);
            }
            DataTable table = dbhelper.ExecuteSp_Page(sql.ToString(), orderCon, pagesize, pageIndex, out records);
            IList<BoTeachers> list = GetList(table);
            return list;
        }

        public IList<BoTeachers> GetTeachersByAcademy(string academyId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT TEA_ID,TEA_CODE,TEA_NAME,TEA_USERNAME,TEA_PASSWORD,TEA_GENDER,TEA_ACADEMY,TEA_MANAGER,A.ACA_NAME,TEA_PHONE,TEA_EMAIL ");
            sql.Append("FROM Teachers AS T LEFT JOIN Academy AS A ON T.TEA_ACADEMY=A.ACA_ID ");
            sql.Append("WHERE TEA_ACADEMY=@ACA_ID  AND TEA_IsShow='true'");
            sql.Append("");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@ACA_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = academyId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoTeachers> list = GetList(table);
            return list;
        }

        /// <summary>
        /// 通过教师编号来获取教师对象
        /// </summary>
        /// <param name="TeacherCode">教师编号</param>
        /// <returns>返回教师对象</returns>
        public BoTeachers GetTeacherByCode(string TeacherCode)
        {
            string sql = "select * FROM Teachers where  TEA_CODE=@TEA_CODE";
            SqlParameter[] parameter = new SqlParameter[1]
            {
                new SqlParameter("@TEA_CODE",SqlDbType.NVarChar,50),
            };
            parameter[0].Value = TeacherCode;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoTeachers> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        private IList<BoTeachers> GetList(DataTable table)
        {
            List<BoTeachers> list = new List<BoTeachers>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoTeachers model = new BoTeachers();
                    model.TEA_ID = (string)table.Rows[i]["TEA_ID"];
                    model.TEA_CODE = (string)table.Rows[i]["TEA_CODE"];
                    model.TEA_NAME = (string)table.Rows[i]["TEA_NAME"];
                    model.TEA_USERNAME = (string)table.Rows[i]["TEA_USERNAME"];
                    model.TEA_PASSWORD = (string)table.Rows[i]["TEA_PASSWORD"];
                    model.TEA_GENDER = (string)table.Rows[i]["TEA_GENDER"];
                    model.TEA_ACADEMY = table.Rows[i]["TEA_ACADEMY"] == System.DBNull.Value ? "" : (string)table.Rows[i]["TEA_ACADEMY"];
                    model.TEA_MANAGER = (Int32)table.Rows[i]["TEA_MANAGER"];
                    model.TEA_PHONE = table.Rows[i]["TEA_PHONE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["TEA_PHONE"];
                    model.TEA_EMAIL = table.Rows[i]["TEA_EMAIL"] == System.DBNull.Value ?"" : (string)table.Rows[i]["TEA_EMAIL"];
                    if (table.Rows[i]["ACA_NAME"] != null)
                        model.ACA_NAME = table.Rows[i]["ACA_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["ACA_NAME"];
                    list.Add(model);
                }
            }
            return list;
        }



        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoTeachers model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@TEA_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@TEA_CODE",SqlDbType.NVarChar,50),
                new SqlParameter("@TEA_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@TEA_USERNAME",SqlDbType.NVarChar,50),
                new SqlParameter("@TEA_PASSWORD",SqlDbType.NVarChar,50),
                new SqlParameter("@TEA_GENDER",SqlDbType.NVarChar,2),
                new SqlParameter("@TEA_ACADEMY",SqlDbType.NVarChar,50),
                new SqlParameter("@TEA_MANAGER",SqlDbType.Int,4),
                new SqlParameter("@TEA_PHONE",SqlDbType.NVarChar,50),
                new SqlParameter("@TEA_EMAIL",SqlDbType.NVarChar,50)
            };
            parameters[0].Value = model.TEA_ID;
            parameters[1].Value = model.TEA_CODE;
            parameters[2].Value = model.TEA_NAME;
            parameters[3].Value = model.TEA_USERNAME;
            parameters[4].Value = model.TEA_PASSWORD;
            parameters[5].Value = model.TEA_GENDER;
            parameters[6].Value = model.TEA_ACADEMY;
            parameters[7].Value = model.TEA_MANAGER;
            parameters[8].Value = model.TEA_PHONE;
            parameters[9].Value = model.TEA_EMAIL;
            return parameters;
        }
    }
}